package com.qin.EducationalManger.score.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.qin.EducationalManger.score.model.ScoreModel;
import com.qin.EducationalManger.score.model.studentScoreModel;
import com.qin.EducationalManger.student.model.StudentModel;
import com.qin.EducationalManger.utils.JdbcUtils;
public class ScoreDao {


		JdbcUtils jdbcUtils = new JdbcUtils();
		public static int insert(ScoreModel model) {

			String sql = "insert into score (course_id,grade_point,score,student_id) values(?,?,?,?)";
			List<Object> vals = Arrays.asList(model.getCourseId(), model.getGradePoint(), model.getScore(),
					model.getStudentId());

			return JdbcUtils.update(sql, vals);

		}
		public static int delet(ScoreModel model) {
			String sql = "delete from scroe where student_id like ? and course_id like ?";
			List<Object> vals = Arrays.asList();
              String studentId = model.getStudentId();
              String scourseId = model.getCourseId();
			
			
			return JdbcUtils.update(sql, vals);

		}
		
		//按照条件修改

		public static int update(ScoreModel model) {

			StringBuffer sql = new StringBuffer("update score set student_id ="+model.getStudentId()); // 储存sql语句
			List<Object> vals = new ArrayList<Object>(); // 用于存储 要修改的 内容
	       
			Double score = model.getScore(); // 判断是否要对该值做出修改
			if (score != null ) {
				sql.append(" ,score = ?");
				vals.add(score);
			}

			Double gradePoint =model.getGradePoint();
			if (gradePoint != null) {
				sql.append(",grade_point = ?");
				vals.add(gradePoint);

			}
		

			sql.append(" where student_id = " + model.getStudentId()+" and course_id ="+model.getCourseId());
			

			return JdbcUtils.update(sql.toString(), vals);

		}
		//查找 多表查找
		public static List<studentScoreModel> selectScore(ScoreModel model){
			
			String studentId = model.getStudentId();
			String courseId = model.getCourseId();
		    String [] courseIds = courseId.split(" ");
		    List<Object> vals = new ArrayList<Object>();
			StringBuffer sql= new StringBuffer("select s.course_id as course_id,d.course_name as course_name,score,grade_point,d.teacher_name as teacher_name,d.title as title from score as s\r\n" + 
					" inner join  \r\n" + 
					" (select c.course_id,course_name ,t.teacher_id ,teacher_name,t.title from course  as c\r\n" + 
					" inner join ( select teacher_id,teacher_name,title from teacher ) as t\r\n" + 
					" on c.teacher_id = t.teacher_id\r\n" + 
					" where course_id in (?");
				
			vals.add(courseIds[0]);
			for(int i =1;i<courseIds.length;i++) {
				sql.append(",?");
				vals.add(courseIds[i]);
			}
				
			sql.append(" ))as d \r\n" + 
					" on  s.course_id= d.course_id\r\n" + 
					"  and student_id= ? \r\n" + 
					" ;");
					
			
			vals.add(studentId);
			
			studentScoreModel scoreModel = new studentScoreModel();
			
			
             Map<String, String> fields = new HashMap<String, String>();
     		
            fields.put("courseName", "course_name");
     		fields.put("courseId", "course_id");
     		fields.put("gradePoint", "grade_point");
     		fields.put("score", "score");
     	 
     		fields.put("title", "title");
     		fields.put("teacherName", "teacher_name");
             
     		
     		
    	return  JdbcUtils.Query(sql.toString(), vals, studentScoreModel.class, fields);

         
             
		}
		
		
		
		
		
		//按照条件查询
		public  static List<ScoreModel> select(ScoreModel model) {
		StringBuffer sql = new StringBuffer("select * from score where 1=1 ");
		
		Map<String, String> fields = new HashMap<String, String>();
		
		fields.put("courseId", "course_id");
		fields.put("gradePoint", "grade_point");
		fields.put("score", "score");
		fields.put("studentId", "student_id");


		
		List<Object> vals = appendWhere(sql, model);
		
		
		return  JdbcUtils.Query(sql.toString(), vals, ScoreModel.class, fields);
		 
		

		 
		
		
		
	     
			
		}
		
		private static List<Object>  appendWhere(StringBuffer sql, ScoreModel model) {
			
			List<Object> vals = new ArrayList<Object>();
			String courseId = model.getCourseId();
			if(courseId!=null&&courseId.intern().isEmpty()==false) {
				sql.append(" and course_id like ?");
				vals.add(courseId);
			}
			Double score =model.getScore();
			if(score!=null) {
				sql.append(" and score=?");
				vals.add(score);
				
			}
			String studentId = model.getStudentId();
			if(studentId!=null&&studentId.intern().isEmpty()==false) {
				sql.append(" and student_id=?");
				vals.add(studentId);	
			}
			Double gradePoint = model.getGradePoint();
			if(gradePoint!=null) {
				sql.append(" and grade_point=?");
				vals.add(gradePoint);
			}
			

			
			if(model.isPageOn()) {
				sql.append("limit ?,?");
				vals.add(model.getRowStart());
				vals.add(model.getPageLimit());
				
			}
			
			return vals;
			
			
		}
		// 查询信息总条数
		public static Integer  selectCount(StudentModel model) {
			String studentId = model.getStudentId();
			String studentName = model.getStudentName();
			List <Object> vals = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("select count(student_id) as count from student where 1=1");
			if(studentId!=null&&studentId.intern().isEmpty()==false) {
				sql.append(" and student_id like ?");
				vals.add(studentId);
			}
			if(studentName!=null&&studentName.intern().isEmpty()==false) {
				sql.append(" and student_name like ?");
				vals.add(studentName);
			}
		
		
			 
			
			return JdbcUtils.selectCount(sql.toString(),vals);
			
		}
	  
	

}
